import pytest
import yaml
import requests

import pymysql

host = "localhost"
port = 3306
user = "root"
password = "root"
database = "finance"


# 专门处理增，删，改
def update(sql, param):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    cursor.execute(sql, param)
    con.commit()
    cursor.close()
    con.close()


# 专门处理查询
def select(sql, param):
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    cursor.execute(sql, param)
    data = cursor.fetchall()
    con.commit()
    cursor.close()
    con.close()
    return data


def delete_user():
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "delete from user where id>20"
    cursor.execute(sql)
    con.commit()
    cursor.close()


def delete_card():
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "delete from bankcard where id>20"
    cursor.execute(sql)
    con.commit()
    cursor.close()


def insert_card():
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "INSERT INTO `finance`.`bankcard` (`id`, `cardBank`, `type`, `cardNum`, `userId`, `balance`, `defaultl`) VALUES (1, '中国建设银行', 2, '12345678945213126', 1, 18310.00, 1)"
    cursor.execute(sql)
    con.commit()
    cursor.close()


def updete_userstatus():
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    sql = "UPDATE `finance`.`user` SET `username` = 'lisi', `realname` = '李四', `password` = 'e10adc3949ba59abbe56e057f20f883e', `IDcard` = '110101199703142123', `phone` = '15188888800', `email` = '123123@qq.com', `paypwd` = 666666, `status` = 1, `reputation` = '良好' WHERE `id` = 1"
    cursor.execute(sql)
    sql = "UPDATE `finance`.`user` SET `username` = 'lisi5', `realname` = '李四5', `password` = 'e10adc3949ba59abbe56e057f20f883e', `IDcard` = '110101199703142123', `phone` = '15188888800', `email` = '123123@qq.com', `paypwd` = 666666, `status` = 0, `reputation` = '良好' WHERE `id` = 10"
    cursor.execute(sql)
    con.commit()
    cursor.close()


def read_yaml(filename):
    # 读取文件
    file = open(file=filename, mode="r+", encoding="utf-8")

    data = yaml.load(file, Loader=yaml.SafeLoader)

    print(data)
    return data
